package com.yikome.shop.service;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import com.yikome.common.CommonService;
import com.yikome.constant.StatusConstant;
import com.yikome.order.entity.Order;
import com.yikome.order.entity.OrderAddress;
import com.yikome.order.entity.OrderExt;
import com.yikome.order.entity.OrderProduct;
import com.yikome.order.enums.OrderStatus;
import com.yikome.order.enums.Payment;
import com.yikome.order.repository.OrderAddressRepository;
import com.yikome.order.repository.OrderExtRepository;
import com.yikome.order.repository.OrderProductRepository;
import com.yikome.order.repository.OrderRepository;
import com.yikome.product.entity.Product;
import com.yikome.product.entity.ProductAttribute;
import com.yikome.product.entity.ProductAttributeValue;
import com.yikome.product.entity.ProductComments;
import com.yikome.product.entity.ProductSet;
import com.yikome.product.entity.ProductSku;
import com.yikome.product.repository.ProductAttributeRepository;
import com.yikome.product.repository.ProductAttributeValueRepository;
import com.yikome.product.repository.ProductCommentsRepository;
import com.yikome.product.repository.ProductRepository;
import com.yikome.product.repository.ProductSetRepository;
import com.yikome.product.repository.ProductSkuRepository;
import com.yikome.promotion.response.ShopCouponResponse;
import com.yikome.promotion.response.ShopFullReductionResponse;
import com.yikome.promotion.service.CouponService;
import com.yikome.promotion.service.FullReductionService;
import com.yikome.shop.api.dto.CartCouponDTO;
import com.yikome.shop.api.dto.CartFullReductionDTO;
import com.yikome.shop.api.dto.CartPriceDTO;
import com.yikome.shop.api.dto.CartProductDTO;
import com.yikome.shop.api.dto.CollectionApiDTO;
import com.yikome.shop.api.dto.FeedBackCreateRequest;
import com.yikome.shop.api.dto.LTPResult;
import com.yikome.shop.api.dto.LTPResultDataInfo;
import com.yikome.shop.api.dto.OrderDTO;
import com.yikome.shop.api.dto.OrderExtPromotionsDTO;
import com.yikome.shop.api.dto.OrderProductDTO;
import com.yikome.shop.api.dto.OrderQueryDTO;
import com.yikome.shop.api.dto.ProductApiDTO;
import com.yikome.shop.api.dto.ProductAttributeApiDTO;
import com.yikome.shop.api.dto.ProductAttributeValueApiDTO;
import com.yikome.shop.api.dto.ProductSkuApiDTO;
import com.yikome.shop.api.dto.ShopApiDTO;
import com.yikome.shop.api.dto.ShopNavApiDTO;
import com.yikome.shop.dto.FeedBackSearchRequest;
import com.yikome.shop.dto.FeedBackSearchResponse;
import com.yikome.shop.entity.FeedBack;
import com.yikome.shop.entity.Navigation;
import com.yikome.shop.entity.Shop;
import com.yikome.shop.repository.FeedBackRepository;
import com.yikome.shop.repository.NavigationRepository;
import com.yikome.shop.repository.ShopRepository;
import com.yikome.shop.request.CartProductRequest;
import com.yikome.shop.request.SubmitCartRequest;
import com.yikome.shop.utils.ExchangeRateUtils;
import com.yikome.utils.JSONUtils;
import com.yikome.utils.LTPUtils;
import com.yikome.utils.NamedParameterJdbcUtils;

import cn.hutool.cache.CacheUtil;
import cn.hutool.cache.impl.TimedCache;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class ShopService extends CommonService<Shop, Long> {

	@Autowired
	private ShopRepository shopRepository ; 
	
	@Autowired
	private NavigationRepository navigationRepository; 
	
	@Autowired
	private ProductRepository productRepository; 
	
	@Autowired
	private ProductAttributeRepository productAttributeRepository;
	
	@Autowired
	private ProductAttributeValueRepository productAttributeValueRepository;
	
	@Autowired
	private ProductSkuRepository productSkuRepository;
	
	@Autowired
	private OrderRepository orderRepository ; 
	
	@Autowired
	private OrderExtRepository orderExtRepository;
	
	@Autowired
	private OrderProductRepository orderProductRepository;
	
	@Autowired
	private OrderAddressRepository orderAddressRepository; 
	
	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate ;
	
	@Autowired
	private FullReductionService fullReductionService ; 
	
	@Autowired
	private FeedBackRepository feedBackRepository ; 
	
	@Autowired
	private ProductCommentsRepository productCommentsRepository ; 
	
	@Autowired
	private ProductSetRepository productSetRepository ; 
	
	@Autowired
	private CouponService couponService ; 
	
	TimedCache<String, String> CACHE = CacheUtil.newTimedCache(60000);
	
	@Autowired
	public void setResourceDao(ShopRepository repository) {
		super.setCommonDao(repository);
	}
	
	public Shop presistShop(Shop shopRequest) {
		if(shopRequest.getId() != null) {
			Shop source = shopRepository.findShop(shopRequest.getId());
			
			BeanUtil.copyProperties(shopRequest, source, "yn" , "id" , "logo" , "createdAt");
			
			return shopRepository.save(source); 
			
		}
		return shopRepository.save(shopRequest);
	}
	
	public Shop saveShop(Shop shopRequest) {
		return shopRepository.save(shopRequest);
	}
	
	public Shop getRequestShop(String requestHost) {
		
		List<Shop> shops = shopRepository.findAll();
		
		if(StrUtil.isBlank(requestHost)) return shops.get(0);
		
		for (Shop shop : shops) {
			
			if(StrUtil.containsIgnoreCase(shop.getLocation(), requestHost)) return shop;
			
		}
		
		return shops.get(0) ;
	}

	public ShopApiDTO findShop(Shop shop) {
		
		ShopApiDTO shopApiDTO = new ShopApiDTO();
		shopApiDTO.setName(shop.getName());
		shopApiDTO.setLocation(shop.getLocation());
		shopApiDTO.setLogo(shop.getLogo());
		
		List<Navigation> navigations = navigationRepository.findNavigation(shop.getId());
		
		List<ShopNavApiDTO> shopNavApiDTOs = organizeNavigation(navigations);
		shopApiDTO.setShopNavs(shopNavApiDTOs);
		
		return shopApiDTO;
	}
	
	private List<ShopNavApiDTO> organizeNavigation(List<Navigation> navigations){
		
		List<ShopNavApiDTO> shopNavApiDTOs = new ArrayList<>();
		
		for (Navigation navigation : navigations) {
			
			if(navigation.getParentId() == null) {
				
				ShopNavApiDTO navApiDTO = new ShopNavApiDTO(navigation.getId(), navigation.getName(), navigation.getThumb(), navigation.getIsShow(), navigation.getPriority(), findNavigationChildren(navigations, navigation));
				shopNavApiDTOs.add(navApiDTO);
				
			}
		}
		return shopNavApiDTOs ;
	}

	private List<ShopNavApiDTO> findNavigationChildren(List<Navigation> navigations, Navigation source) {
		List<ShopNavApiDTO> subs = new ArrayList<>();
		for (Navigation navigation : navigations) {
			if(source.getId().equals(navigation.getParentId())) {
				ShopNavApiDTO navApiDTO = new ShopNavApiDTO(navigation.getId(), navigation.getName(), navigation.getThumb(), navigation.getIsShow(), navigation.getPriority(), null);
				subs.add(navApiDTO);
			}
		}
		return subs;
	}
	
	public CollectionApiDTO findCollection(Long collectionId) {
		
		String contentSQL = "SELECT \n" + 
							"	s_collection.id collectionId , s_collection.name collectionName, s_collection.banners, s_collection.cover_img, s_collection.comments, s_present.present, s_present.template, s_present.script \n" + 
							"FROM s_collection \n" + 
							"	LEFT JOIN s_present ON (s_collection.present_id = s_present.id) \n" + 
							"WHERE s_collection.id = :collectionId \n"; 
		
		CollectionApiDTO collectionApiDTO = null ; 
		try {
			collectionApiDTO = jdbcTemplate.queryForObject(contentSQL, ImmutableMap.of("collectionId", collectionId), new BeanPropertyRowMapper<CollectionApiDTO>(CollectionApiDTO.class));
		}catch (Exception e) {
			return null ; 
		}
		
		String collectionProductSQL =   "SELECT \n" + 
										"	p_product.id, p_product.title, p_product.image, p_product.description, p_product.tags, p_product.price, p_product.original_price, p_product.cover_img \n" + 
										"FROM s_collection_product \n" + 
										"	LEFT JOIN p_product ON (s_collection_product.product_id = p_product.id) \n" + 
										"WHERE 1 = 1 \n" + 
										"AND s_collection_product.collection_id = :collectionId \n" + 
										"AND p_product.yn = 1 \n" + 
										"AND p_product.`status` = 10 \n" +
										"ORDER BY s_collection_product.priority ASC " ;  

		List<ProductApiDTO> productApiDTOs = jdbcTemplate.query(collectionProductSQL, ImmutableMap.of("collectionId", collectionApiDTO.getCollectionId()), new BeanPropertyRowMapper<ProductApiDTO>(ProductApiDTO.class));
		for (ProductApiDTO productApiDTO : productApiDTOs) {
			productApiDTO.setTagLists(StrUtil.splitTrim(productApiDTO.getTags(), ","));
		}
		collectionApiDTO.setProductDTOs(productApiDTOs);
		
		collectionApiDTO.setBannerLists(createBannerLists(collectionApiDTO.getBanners()));
		
		return collectionApiDTO;
	}

	public List<CollectionApiDTO> findNavCollections(List<String> navIds) {
		
		String contentSQL = "SELECT \n" + 
							"	s_collection.id collectionId , s_collection.name collectionName, s_collection.banners, s_collection.cover_img, s_collection.comments, s_present.present, s_present.template, s_present.script \n" + 
							"FROM s_nav_collection \n" + 
							"	LEFT JOIN s_collection ON (s_nav_collection.collection_id = s_collection.id) \n" + 
							"	LEFT JOIN s_present ON (s_collection.present_id = s_present.id) \n" + 
							"WHERE s_nav_collection.nav_id IN (:navIds) \n" +
							"ORDER BY s_nav_collection.priority ASC " ; 
		
		List<CollectionApiDTO> collectionApiDTOs = jdbcTemplate.query(contentSQL, ImmutableMap.of("navIds", navIds), new BeanPropertyRowMapper<CollectionApiDTO>(CollectionApiDTO.class));
		
		for (CollectionApiDTO collectionApiDTO : collectionApiDTOs) {
			
			String collectionProductSQL =   "SELECT \n" + 
											"	p_product.id, p_product.title, p_product.image, p_product.description, p_product.tags, p_product.price, p_product.original_price, p_product.cover_img \n" + 
											"FROM s_collection_product \n" + 
											"	LEFT JOIN p_product ON (s_collection_product.product_id = p_product.id) \n" + 
											"WHERE 1 = 1 \n" + 
											"AND s_collection_product.collection_id = :collectionId \n" + 
											"AND p_product.yn = 1 \n" + 
											"AND p_product.`status` = 10 \n" +
											"ORDER BY s_collection_product.priority ASC " ;  
			
			List<ProductApiDTO> productApiDTOs = jdbcTemplate.query(collectionProductSQL, ImmutableMap.of("collectionId", collectionApiDTO.getCollectionId()), new BeanPropertyRowMapper<ProductApiDTO>(ProductApiDTO.class));
			for (ProductApiDTO productApiDTO : productApiDTOs) {
				productApiDTO.setTagLists(StrUtil.splitTrim(productApiDTO.getTags(), ","));
			}
			
			collectionApiDTO.setProductDTOs(productApiDTOs);
			
			collectionApiDTO.setBannerLists(createBannerLists(collectionApiDTO.getBanners()));
		}
		
		return collectionApiDTOs;
	}
	
	private List<Map<String , String>> createBannerLists(String banners) {
		List<Map<String , String>> bannerLists = new ArrayList<>();
		if(StrUtil.isBlank(banners)) return bannerLists;
		
		List<String> splitBanners = StrUtil.splitTrim(banners, ";");
		if(splitBanners != null && !splitBanners.isEmpty()) {
			for (String splitBanner : splitBanners) {
				List<String> banerKV = StrUtil.splitTrim(splitBanner, "@#@");
				if(banerKV.size() == 1)banerKV.add("#");
				Map<String , String> banerMap = new HashMap<>();
				banerMap.put("url", banerKV.get(0));
				banerMap.put("location", StrUtil.isBlank(banerKV.get(1).trim()) ? "#" : banerKV.get(1).trim());
				bannerLists.add(banerMap);
			}
		}
		return bannerLists;
	}

	public ProductApiDTO findProduct(Long productId) {
		
		String productSQL = "SELECT \n" + 
							"	p_product.id, p_product.title, p_product.image, p_product.description, p_product.tags, p_product.price, p_product.original_price, p_product.`status`, p_product_ext.carousel_images, p_product_ext.product_details, p_product_ext.product_props, p_product.cover_img \n" + 
							"FROM p_product  \n" + 
							"	LEFT JOIN p_product_ext ON (p_product.id = p_product_ext.product_id) \n" + 
							"WHERE p_product.yn = 1  \n" + 
							"AND p_product.id = :productId \n" ; 
		
		ProductApiDTO productApiDTO = jdbcTemplate.queryForObject(productSQL, ImmutableMap.of("productId", productId), new BeanPropertyRowMapper<ProductApiDTO>(ProductApiDTO.class));
		
    	List<ProductAttributeApiDTO> productAttributeApiDTOs = new ArrayList<>();
    	List<ProductAttribute> productAttributes = productAttributeRepository.findByProductId(productId);
    	for (ProductAttribute productAttribute : productAttributes) {
    		ProductAttributeApiDTO productAttributeApiDTO = new ProductAttributeApiDTO();
    		BeanUtil.copyProperties(productAttribute, productAttributeApiDTO);
    		List<ProductAttributeValue> productAttributeValues = productAttributeValueRepository.findAllProductAttributeValue(productId, productAttribute.getId());
    		
    		List<ProductAttributeValueApiDTO> productAttributeValueApiDTOs = new ArrayList<>();
    		for (ProductAttributeValue productAttributeValue : productAttributeValues) {
    			ProductAttributeValueApiDTO productAttributeValueApiDTO = new ProductAttributeValueApiDTO();
    			BeanUtil.copyProperties(productAttributeValue, productAttributeValueApiDTO);
    			productAttributeValueApiDTOs.add(productAttributeValueApiDTO);
			}
    		
    		productAttributeApiDTO.setProductAttributeValueApiDTOs(productAttributeValueApiDTOs);
    		productAttributeApiDTOs.add(productAttributeApiDTO);
		}
    	productApiDTO.setProductAttributeApiDTOs(productAttributeApiDTOs);
    	
    	List<ProductSkuApiDTO> productSkuApiDTOs = new ArrayList<>();
    	List<ProductSku> productSkus = productSkuRepository.findAllByProductId(productId);
    	for (ProductSku productSku : productSkus) {
    		ProductSkuApiDTO productSkuApiDTO = new ProductSkuApiDTO();
    		BeanUtil.copyProperties(productSku, productSkuApiDTO);
    		
    		StringBuilder code = new StringBuilder(String.valueOf(productId));
    		if(productSkuApiDTO.getProductAttributeValueId1() != null) code.append("-").append(productSkuApiDTO.getProductAttributeValueId1());
    		if(productSkuApiDTO.getProductAttributeValueId2() != null) code.append("-").append(productSkuApiDTO.getProductAttributeValueId2());
    		productSkuApiDTO.setCode(code.toString());
    		productSkuApiDTOs.add(productSkuApiDTO);
		}
    	productApiDTO.setProductSkuApiDTOs(productSkuApiDTOs);
		return productApiDTO;
	}
	
	public List<ProductApiDTO> findLikeProduct(Long productId){
    	String likeSQL = "SELECT  \n" + 
			    			"	DISTINCT p_product.id, p_product.title, p_product.image, p_product.description, p_product.tags, p_product.price, p_product.original_price, p_product.cover_img \n" + 
			    			"FROM s_collection_product c1 \n" + 
			    			"	LEFT JOIN s_collection_product c2 ON (c1.collection_id = c2.collection_id) \n" + 
			    			"	LEFT JOIN p_product ON (c2.product_id = p_product.id) \n" + 
			    			"WHERE 1 = 1 \n" + 
			    			"AND c1.product_id = :productId \n" + 
			    			"AND p_product.yn = 1 AND p_product.`status` = 10 \n" +
			    			"AND p_product.id != :productId \n" +
			    			"LIMIT 0 , 6";
    	
    	List<ProductApiDTO> productApiDTOs = jdbcTemplate.query(likeSQL, ImmutableMap.of("productId", productId), new BeanPropertyRowMapper<ProductApiDTO>(ProductApiDTO.class));
    	return productApiDTOs ;
	}
	
	public List<ProductApiDTO> findSimilarProduct(Long productId){
    	String similarSQL = "SELECT  \n" + 
			    			"	p_product.id, p_product.title, p_product.image, p_product.description, p_product.tags, p_product.price, p_product.original_price, p_product.cover_img \n" + 
			    			"FROM p_product \n" + 
			    			"WHERE 1 = 1 \n" + 
			    			"AND p_product.yn = 1 \n"+
			    			"AND p_product.`status` = 10 \n" +
			    			"AND p_product.id IN (SELECT p_product_ext.product_id FROM p_product_ext WHERE p_product_ext.category_id = (SELECT p_product_ext.category_id FROM p_product_ext WHERE p_product_ext.product_id = :productId)) \n" + 
			    			"LIMIT 0 , 20";
    	
    	List<ProductApiDTO> productApiDTOs = jdbcTemplate.query(similarSQL, ImmutableMap.of("productId", productId), new BeanPropertyRowMapper<ProductApiDTO>(ProductApiDTO.class));
    	return productApiDTOs ;
	}
	
	public List<ProductApiDTO> findBottomProduct(Long productId){
    	String similarSQL = "SELECT  \n" + 
			    			"	p_product.id, p_product.title, p_product.image, p_product.description, p_product.tags, p_product.price, p_product.original_price, p_product.cover_img \n" + 
			    			"FROM p_product \n" + 
			    			"WHERE 1 = 1 \n" + 
			    			"AND p_product.yn = 1 \n"+
			    			"AND p_product.`status` = 10 \n" +
			    			"AND p_product.id IN (SELECT p_product_ext.product_id FROM p_product_ext WHERE p_product_ext.category_id = (SELECT p_product_ext.category_id FROM p_product_ext WHERE p_product_ext.product_id = :productId)) \n" + 
			    			"LIMIT 0 , 20";
    	
    	List<ProductApiDTO> productApiDTOs = jdbcTemplate.query(similarSQL, ImmutableMap.of("productId", productId), new BeanPropertyRowMapper<ProductApiDTO>(ProductApiDTO.class));
    	return productApiDTOs ;
	}
	
	public List<ProductComments> findProductComments(Long productId){
		return productCommentsRepository.findByProductId(productId, Sort.by(Direction.DESC, "id")); 
	}
	
	public List<ProductSet> findProductSets(Long productId){
		return productSetRepository.findByProductId(productId);
	}
	
	private List<CartProductDTO> filterCartProductRequest(List<CartProductRequest> cartProductRequests , BigDecimal exchangeRate){
		
		List<CartProductDTO> cartProductDTOs = new ArrayList<>();
		
		for (CartProductRequest cartProductRequest : cartProductRequests) {
			
			Product product = productRepository.findByProductId(cartProductRequest.getProductId());
			
			if(product != null && StatusConstant.PRODUCT_STATUS_ONLINE_UP.equals(product.getStatus())) {
				
				ProductSku productSku = productSkuRepository.findByBarcode(cartProductRequest.getProductSkuBarcode());
				
				if(productSku != null && product.getId().equals(productSku.getProductId())) {
					
					CartProductDTO cartProductDTO = new CartProductDTO() ; 
					cartProductDTO.setProductId(product.getId());
					cartProductDTO.setProductImageUrl(StrUtil.isBlank(productSku.getImage()) ? product.getImage() : productSku.getImage());
					cartProductDTO.setProductPrice(ExchangeRateUtils.filterPrice(productSku.getPrice(), exchangeRate));
					cartProductDTO.setOriginalProductPrice(cartProductRequest.getOriginalProductPrice() == null ? BigDecimal.ZERO : cartProductRequest.getOriginalProductPrice());
					cartProductDTO.setProductSkuBarcode(productSku.getBarcode());
					cartProductDTO.setProductSkuId(productSku.getId());
					cartProductDTO.setProductSkuName(productSku.getName());
					cartProductDTO.setProductTitle(product.getTitle());
					cartProductDTO.setQuantity(cartProductRequest.getQuantity() != null ? Math.abs(cartProductRequest.getQuantity()) : 1);
					cartProductDTO.setProductTotalPrice(cartProductDTO.getProductPrice().multiply(BigDecimal.valueOf(cartProductDTO.getQuantity())));
					cartProductDTO.setChecked(cartProductRequest.getChecked());
					
					cartProductDTOs.add(cartProductDTO);
				}
			}
		}
		return cartProductDTOs ; 
	}
	
	/**
	 * 购物车价格计算
	 */
	
	public CartPriceDTO caculateCartPrice(SubmitCartRequest submitCartRequest , Shop shop) {
		
		List<CartProductRequest> cartProductRequests = submitCartRequest.getCartProductRequests();
		
		CartPriceDTO cartPriceDTO = new CartPriceDTO();
		
		BigDecimal cartPrice = BigDecimal.ZERO;
		List<CartProductDTO> cartProductDTOs = filterCartProductRequest(cartProductRequests , shop.getExchangeRate());
		
		for (CartProductDTO cartProductDTO : cartProductDTOs) {
			if(cartProductDTO.getChecked() == null) cartProductDTO.setChecked(Boolean.FALSE);
			cartPrice = cartPrice.add(cartProductDTO.getChecked() ? cartProductDTO.getProductTotalPrice() : BigDecimal.ZERO);
		}
		
		List<ShopFullReductionResponse> fullReductionResponses = fullReductionService.findShopFullReductionResponse(shop.getId() , cartPrice) ;
		if(!fullReductionResponses.isEmpty()) {
			ShopFullReductionResponse reductionResponse = fullReductionResponses.get(0);
			
			CartFullReductionDTO cartFullReductionDTO = new CartFullReductionDTO() ; 
			BeanUtil.copyProperties(reductionResponse, cartFullReductionDTO);
			
			BigDecimal promotionPrice = (cartPrice.compareTo(reductionResponse.getReductionValue()) >= 0) ? reductionResponse.getReductionValue() : cartPrice ; 
			cartFullReductionDTO.setPromotionPrice(promotionPrice);
			
			cartPrice = cartPrice.subtract(promotionPrice);
			
			cartPriceDTO.setFullReductionPromotion(cartFullReductionDTO);
		}
		
		if(StrUtil.isNotBlank(submitCartRequest.getCouponCode())) {
			ShopCouponResponse shopCouponResponse = couponService.findShopCouponResponse(shop.getId(), submitCartRequest.getCouponCode());
			if(shopCouponResponse != null) {
				CartCouponDTO cartCouponDTO = new CartCouponDTO() ; 
				BeanUtil.copyProperties(shopCouponResponse, cartCouponDTO);
				
				BigDecimal promotionPrice = (cartPrice.compareTo(shopCouponResponse.getReductionValue()) >= 0) ? shopCouponResponse.getReductionValue() : cartPrice ; 
				cartCouponDTO.setPromotionPrice(promotionPrice);
				
				cartPrice = cartPrice.subtract(promotionPrice);
				
				cartPriceDTO.setCartCoupon(cartCouponDTO);
			}
		}
		
		cartPriceDTO.setFreight(cartPrice.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO : cartPrice.compareTo(shop.getBasicPrice()) >= 0 ? BigDecimal.ZERO : shop.getFreight());
		
		cartPriceDTO.setPrice(cartPrice.add(cartPriceDTO.getFreight()));
		
		cartPriceDTO.setCartProductDTOs(cartProductDTOs);
		
		return cartPriceDTO ; 
	}
	
	public CartPriceDTO caculateSetsPrice(SubmitCartRequest submitCartRequest , Shop shop) {
		
		List<CartProductRequest> cartProductRequests = submitCartRequest.getCartProductRequests();
		
		Set<Long> productIdSets = cartProductRequests.stream().map(CartProductRequest::getProductId).collect(Collectors.toSet());
		Preconditions.checkArgument(productIdSets.size() == 1, "not sets purchased !!!");
		
		CartPriceDTO cartPriceDTO = new CartPriceDTO();
		
		BigDecimal cartPrice = BigDecimal.ZERO;
		List<CartProductDTO> cartProductDTOs = filterCartProductRequest(cartProductRequests , shop.getExchangeRate());
		
		//是否存在sets活动
		ProductSet usedProductSet = null ;
		List<ProductSet> productSets = productSetRepository.findByProductId(cartProductRequests.get(0).getProductId());
		for (ProductSet productSet : productSets) {
			if(productSet.getSetNumber().intValue() == cartProductRequests.size()) {
				usedProductSet = productSet ; 
			}
		}
		Preconditions.checkArgument(usedProductSet != null, "not sets purchased !!!");
		
		for (CartProductDTO cartProductDTO : cartProductDTOs) {
			cartProductDTO.setQuantity(1);
			cartProductDTO.setProductPrice(usedProductSet.getSetPrice().divide(BigDecimal.valueOf(cartProductRequests.size()), 2, BigDecimal.ROUND_HALF_DOWN));
			cartProductDTO.setProductTotalPrice(cartProductDTO.getProductPrice());
		}
		
		cartPrice = usedProductSet.getSetPrice();
		
		cartPriceDTO.setFreight(cartPrice.compareTo(BigDecimal.ZERO) == 0 ? BigDecimal.ZERO : cartPrice.compareTo(shop.getBasicPrice()) >= 0 ? BigDecimal.ZERO : shop.getFreight());
		
		cartPriceDTO.setPrice(cartPrice.add(cartPriceDTO.getFreight()));
		
		cartPriceDTO.setCartProductDTOs(cartProductDTOs);
		
		return cartPriceDTO ; 
	}
	
	
	
	/**
	 * 提交订单COD
	 * TODO
	 */
	@Transactional
	public Order submitCart(SubmitCartRequest submitCartRequest , Shop shop , String ip, Boolean needAsync) {
		//整理产品
		
		List<CartProductRequest> cartProductRequests = submitCartRequest.getCartProductRequests();
		for (CartProductRequest cartProductRequest : cartProductRequests) {
			cartProductRequest.setChecked(Boolean.TRUE);
		}
		submitCartRequest.setCartProductRequests(cartProductRequests);
		
		CartPriceDTO caculateCartPrice = null;
		if(StrUtil.isNotBlank(submitCartRequest.getSource()) && StrUtil.equalsIgnoreCase(submitCartRequest.getSource(), "set")) {
			caculateCartPrice = caculateSetsPrice(submitCartRequest, shop);
		}else {
			caculateCartPrice = caculateCartPrice(submitCartRequest, shop);
		}
		
		
		Order order = new Order() ; 
		orderRepository.save(order);

		order.setCode(shop.getPrefix() + LocalDateTime.now(ZoneId.of("+8")).format(DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS")) + RandomUtil.randomInt(10, 99));
		order.setFreight(caculateCartPrice.getFreight());
		order.setPayment(submitCartRequest.getPayment());
		order.setPrice(caculateCartPrice.getPrice());
		order.setRemark(submitCartRequest.getRemark());
		order.setStatus(OrderStatus.ORDER_REVIEW.getStatus());
		order.setCurrency(shop.getCurrency());
		order.setNeedAsync(needAsync);
		order.setIsAsync(Boolean.FALSE);
		orderRepository.save(order);
		
		OrderExt orderExt = new OrderExt();
		orderExt.setAdlk(submitCartRequest.getAdlk());
		orderExt.setAffiliate(submitCartRequest.getAffiliate());
		orderExt.setIp(ip);
		orderExt.setOrderCode(order.getCode());
		orderExt.setPixelId(submitCartRequest.getPixelId());
		orderExt.setShopId(shop.getId());
		orderExt.setShopName(shop.getName());
		
		//增加优惠信息
		List<OrderExtPromotionsDTO> promotions = new ArrayList<>();
		if(caculateCartPrice.getFullReductionPromotion() != null) {
			promotions.add(OrderExtPromotionsDTO.builder().promotionName(caculateCartPrice.getFullReductionPromotion().getPromotionName()).promotionCode(caculateCartPrice.getFullReductionPromotion().getPromotionCode()).promotionPrice(caculateCartPrice.getFullReductionPromotion().getPromotionPrice()).build());
		}
		if(caculateCartPrice.getCartCoupon() != null) {
			promotions.add(OrderExtPromotionsDTO.builder().promotionName(caculateCartPrice.getCartCoupon().getPromotionName()).promotionCode(caculateCartPrice.getCartCoupon().getPromotionCode()).promotionPrice(caculateCartPrice.getCartCoupon().getPromotionPrice()).build());
		}
		orderExt.setPromotions(JSONUtils.toJsonString(promotions));
		orderExtRepository.save(orderExt);
		
		OrderAddress orderAddress = new OrderAddress();
		orderAddress.setCity(submitCartRequest.getSubmitAddressRequest().getCity());
		orderAddress.setConsignee(submitCartRequest.getSubmitAddressRequest().getConsignee());
		orderAddress.setContry(submitCartRequest.getSubmitAddressRequest().getContry());
		orderAddress.setDetails(submitCartRequest.getSubmitAddressRequest().getDetails());
		orderAddress.setOrderCode(order.getCode());
		orderAddress.setProvince(submitCartRequest.getSubmitAddressRequest().getProvince());
		orderAddress.setTel(submitCartRequest.getSubmitAddressRequest().getTel());
		orderAddress.setDistrict(submitCartRequest.getSubmitAddressRequest().getDistrict());
		orderAddress.setEmail(submitCartRequest.getSubmitAddressRequest().getEmail());
		orderAddress.setZipCode(submitCartRequest.getSubmitAddressRequest().getZipCode());
		orderAddressRepository.save(orderAddress);
		
		List<OrderProduct> orderProducts = new ArrayList<>();
		List<CartProductDTO> cartProductDTOs = caculateCartPrice.getCartProductDTOs();
		for (CartProductDTO cartProductDTO : cartProductDTOs) {
			OrderProduct orderProduct = new OrderProduct();
			orderProduct.setOrderCode(order.getCode());
			orderProduct.setProductId(cartProductDTO.getProductId());
			orderProduct.setProductImageUrl(cartProductDTO.getProductImageUrl());
			orderProduct.setProductPrice(cartProductDTO.getProductPrice());
			orderProduct.setProductSkuBarcode(cartProductDTO.getProductSkuBarcode());
			orderProduct.setProductSkuId(cartProductDTO.getProductSkuId());
			orderProduct.setProductSkuName(cartProductDTO.getProductSkuName());
			orderProduct.setProductTitle(cartProductDTO.getProductTitle());
			orderProduct.setProductTotalPrice(cartProductDTO.getProductTotalPrice());
			orderProduct.setQuantity(cartProductDTO.getQuantity());
			orderProducts.add(orderProduct);
		}
		orderProductRepository.saveAll(orderProducts);
		return order ; 
	}
	
	//TODO
	@Transactional
	public Order stripeSubmitCart(SubmitCartRequest submitCartRequest , Shop shop , String ip) {
		Order order = submitCart(submitCartRequest, shop, ip, Boolean.FALSE);
		order.setStatus(OrderStatus.ORDER_PAY.getStatus());
		orderRepository.save(order);
		return order;
	}
	
	public Order findOrder(String orderCode) {
		return orderRepository.findOrder(orderCode);
	}
	
	@Transactional
	public Order successStripe(Order order) {
		order.setStatus(OrderStatus.ORDER_REVIEW.getStatus());
		order.setNeedAsync(Boolean.TRUE);
		orderRepository.save(order);
		return order;
	}
	

	
	

	public OrderQueryDTO findUserOrder(String querys , Shop shop) {
		
		String orderContetSQL = "SELECT \n" + 
								"	o_order.`code`, o_order.payment, o_order.price, o_order.freight, o_order.remark, o_order.`status`, o_order.created_at , o_order_ext.promotions, \n" + 
								"	o_order_address.contry, o_order_address.province, o_order_address.city, o_order_address.details, o_order_address.district, o_order_address.consignee, o_order_address.tel, \n" + 
								"	o_order_address.email, o_order_address.zip_code \n" + 
								"FROM o_order  \n" + 
								"	LEFT JOIN o_order_ext ON (o_order.`code` = o_order_ext.order_code)  \n" + 
								"	LEFT JOIN o_order_address ON (o_order.`code` = o_order_address.order_code) \n" + 
								"WHERE 1 = 1 \n" + 
								"AND o_order.yn = 1 \n" + 
								"AND o_order_ext.shop_id = :shopId \n" + 
								"AND (o_order.`code` = :querys OR o_order_address.tel = :querys) \n" +
								"ORDER BY o_order.created_at ASC "; 
		
		List<OrderDTO> orderDTOs = jdbcTemplate.query(orderContetSQL, ImmutableMap.of("shopId", shop.getId(), "querys", querys), new BeanPropertyRowMapper<>(OrderDTO.class));
		
		for (OrderDTO orderDTO : orderDTOs) {
			
			orderDTO.setPaymentName(Payment.name(orderDTO.getPayment()).getName());
			orderDTO.setStatusName(OrderStatus.OrderStatusByStatus(orderDTO.getStatus()).getStatusName());
			
			orderDTO.setTel(StrHideUtils.hideStr(orderDTO.getTel()));
			orderDTO.setConsignee(StrHideUtils.hideStr(orderDTO.getConsignee()));
			orderDTO.setDetails(StrHideUtils.hideStr(orderDTO.getDetails()));
			orderDTO.setEmail(StrHideUtils.hideStr(orderDTO.getEmail()));
			
			
			String orderProductSQL ="SELECT \n" + 
									"	o_order_product.product_id, o_order_product.product_title, o_order_product.product_sku_barcode, o_order_product.product_sku_name, o_order_product.product_price, o_order_product.product_image_url, o_order_product.quantity, o_order_product.product_total_price \n" + 
									"FROM o_order_product \n" + 
									"WHERE 1 = 1 \n" + 
									"AND o_order_product.yn = 1 \n" + 
									"AND o_order_product.order_code = :orderCode \n" ; 
			
			orderDTO.setOrderProductDTOs(jdbcTemplate.query(orderProductSQL, ImmutableMap.of("orderCode", orderDTO.getCode()), new BeanPropertyRowMapper<>(OrderProductDTO.class)));
		}
		

		
		OrderQueryDTO orderQueryDTO = new OrderQueryDTO();
		orderQueryDTO.setOrderDTOs(orderDTOs);
		return orderQueryDTO ;
	}

	public List<Map<String, Object>> findShopCombo() {
		String contentSQL = "SELECT '' AS 'id' , '全部商城' AS 'text' FROM DUAL \n" + 
							"UNION \n" + 
							"SELECT s_shop.id , s_shop.`name` FROM s_shop \n" ;
		return jdbcTemplate.queryForList(contentSQL, ImmutableMap.of());
	}
	
	public List<Map<String, Object>> findShopHostCombo() {
		String contentSQL = "SELECT '' AS 'id' , '全部商城' AS 'text' FROM DUAL \n" + 
							"UNION \n" + 
							"SELECT s_shop.location , s_shop.`name` FROM s_shop \n" ;
		return jdbcTemplate.queryForList(contentSQL, ImmutableMap.of());
	}

	public List<ProductApiDTO> findRecomendHosts(Shop shop) {
		
		String hotProductSQL =  "SELECT \n" + 
								"	p_product.id, p_product.title, p_product.image, p_product.description, p_product.tags, p_product.price, p_product.original_price, p_product.cover_img \n" + 
								"FROM  \n" + 
								"( \n" + 
								"	SELECT \n" + 
								"		o_order_product.product_id, SUM(o_order_product.quantity) quantities \n" + 
								"	FROM o_order_ext \n" + 
								"		LEFT JOIN o_order_product ON (o_order_ext.order_code = o_order_product.order_code) \n" + 
								"	WHERE 1 = 1 \n" + 
								"	AND o_order_ext.shop_id = :shopId \n" + 
								"	AND o_order_product.yn = 1 \n" + 
								"	GROUP BY o_order_product.product_id \n" +
								") v	LEFT JOIN p_product ON (v.product_id = p_product.id) \n" + 
								"WHERE p_product.yn  = 1 \n" + 
								"AND p_product.`status` = 10 \n" + 
								"ORDER BY v.quantities DESC LIMIT 20 \n" ; 

		List<ProductApiDTO> hotProducts = jdbcTemplate.query(hotProductSQL, ImmutableMap.of("shopId", shop.getId()), new BeanPropertyRowMapper<>(ProductApiDTO.class));
		
		for (ProductApiDTO productApiDTO : hotProducts) {
			productApiDTO.setTagLists(StrUtil.splitTrim(productApiDTO.getTags(), ","));
		}
		return hotProducts ;
	}

	public List<ProductApiDTO> findSearchProduct(String keywords, Pageable pageable) {
		
		String contentSQL = "SELECT \n" + 
							"	p_product.id, p_product.title, p_product.image, p_product.description, p_product.tags, p_product.price, p_product.original_price, p_product.cover_img \n" + 
							"FROM p_product \n" + 
							"WHERE p_product.yn = 1 \n" + 
							"AND p_product.`status` = 10 \n" + 
							"AND p_product.title LIKE :productTitle \n" ;
		
		String pageSQL = NamedParameterJdbcUtils.findPageSQL(pageable);
		
		ImmutableMap<String, String> paramsMap = ImmutableMap.of("productTitle", StrUtil.wrap(keywords, "%"));
		
		List<ProductApiDTO> searchProducts = jdbcTemplate.query(contentSQL + pageSQL, paramsMap, new BeanPropertyRowMapper<>(ProductApiDTO.class));
		
		for (ProductApiDTO productApiDTO : searchProducts) {
			productApiDTO.setTagLists(StrUtil.splitTrim(productApiDTO.getTags(), ","));
		}
		
		return searchProducts ; 
	}

	public List<String> findShopKeywords(Shop shop) {
		
		log.info("{} {} {}" , "ShopService" , "findShopKeywords" , shop.getName());
		
		String shopKey = shop.getId() + "-" + shop.getName() ; 
		
		String cacheValue = CACHE.get(shopKey);
		
		if(StrUtil.isBlank(cacheValue)) {
			String contentSQL = "SELECT GROUP_CONCAT(DISTINCT p_product.title) FROM p_product WHERE p_product.yn = 1 AND p_product.`status` = 10" ;
			
			String databaseText = jdbcTemplate.queryForObject(contentSQL, ImmutableMap.of(), String.class);
			
			String keywordExtractionp = LTPUtils.keywordExtractionp(databaseText);
			
			log.info("keywordExtractionp : {}" , keywordExtractionp);
			
			if(keywordExtractionp == null) return ImmutableList.of();
			
			LTPResult ltpResult = JSONUtils.parseJson(keywordExtractionp, LTPResult.class);
			
			if(StrUtil.equals(ltpResult.getCode(), "0")) {
			
				List<String> results = new ArrayList<>();
				
				List<LTPResultDataInfo> kes = ltpResult.getData().getKe();
				
				for (LTPResultDataInfo ltpResultDataInfo : kes) {
					results.add(ltpResultDataInfo.getWord());
				}
				
				CACHE.put(shopKey, JSONUtils.toJsonString(results));
				return results ;
			}
			
			return ImmutableList.of();
		}else {
			return JSONUtils.parseJsonList(cacheValue, String.class);
		}
	}
	
	public Boolean exsitProductId(Long productId) {
		
		Product product = productRepository.findByProductId(productId);
		
		if(product == null) return Boolean.TRUE;
		
		if(product.getStatus() == StatusConstant.PRODUCT_STATUS_ONLINE_UP)return Boolean.TRUE;
		
		return Boolean.FALSE ;
	}

	@Transactional
	public void createFeedBack(FeedBackCreateRequest feedBackCreateRequest) {

		FeedBack feedBack = new FeedBack();
		feedBack.setShopId(feedBackCreateRequest.getShopId());
		feedBack.setTel(feedBackCreateRequest.getTel());
		feedBack.setFeedback(feedBackCreateRequest.getText());
		
		feedBackRepository.save(feedBack);
	}

	public Page<FeedBackSearchResponse> findFeedBackSearchResponse(FeedBackSearchRequest feedBackSearchRequest, Pageable pageable) {
		
		String contentSQL = "select s_feedback.*, s_shop.name shop_name from s_feedback left join s_shop on (s_feedback.shop_id = s_shop.id) where 1 = 1" ;
		
		StringBuilder paramSQL = new StringBuilder() ;
		Map<String, Object> paramMap = new HashMap<>();
		
		if(feedBackSearchRequest.getShopId() != null) {
			paramSQL.append(" and s_feedback.shop_id = :shopId ");
			paramMap.put("shopId", feedBackSearchRequest.getShopId());
		}
		
		if(StrUtil.isNotBlank(feedBackSearchRequest.getTel())) {
			paramSQL.append(" and s_feedback.tel like :tel ");
			paramMap.put("tel", "%" + feedBackSearchRequest.getTel().trim() + "%");
		}
		
		if(feedBackSearchRequest.getBegainTime() != null && feedBackSearchRequest.getEndTime() != null) {
			paramSQL.append(" AND s_feedback.created_at BETWEEN FROM_UNIXTIME(:begainTime) AND FROM_UNIXTIME(:endTime) \n");
			paramMap.put("begainTime", feedBackSearchRequest.getBegainTime());
			paramMap.put("endTime", feedBackSearchRequest.getEndTime());
    	}
		
		String orderSQL = NamedParameterJdbcUtils.findOrderSQL(pageable);
    	String pageSQL = NamedParameterJdbcUtils.findPageSQL(pageable);
		
    	List<FeedBackSearchResponse> content = jdbcTemplate.query(contentSQL + paramSQL + orderSQL + pageSQL , paramMap, new BeanPropertyRowMapper<>(FeedBackSearchResponse.class));
    	
    	Long total = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM (" + contentSQL + paramSQL + ") T", paramMap, Long.class);

        return new PageImpl<>(content, pageable, total);
	}
}
